﻿Imports MySql.Data.MySqlClient

Public Class CheckDB_Agenda
    Inherits CheckDB

    Public oProgressBar As Windows.Forms.ProgressBar
    Public oLabel As Windows.Forms.Label
    Public oConnectionString As ConnectionString
    Dim strNomeDb As String

    Dim strNameTable As String = ""
    Const intNumeroTabelle As Integer = 92

    Public Overrides Function ControllaDB(ByVal inConControlloIniziale As Boolean) As Boolean
        strNomeDb = oConnectionString.Database
        Return MyBase.ControllaDB(inConControlloIniziale)
    End Function

    Protected Overrides Function Controlla(ByVal inConControlloIniziale As Boolean) As Boolean
        Dim trTrans As MySqlTransaction = Nothing
        Try
            Using cnConnection As New MySqlConnection(String.Format("host={0}; username={1}; password={2}", oConnectionString.Host, oConnectionString.UserName, oConnectionString.Password))
                cnConnection.Open()
                Dim strSql As String = String.Format("CREATE DATABASE if not EXISTS `{0}` /*!40100 DEFAULT CHARACTER SET latin1*/ ", strNomeDb)
                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using
            End Using

            If oProgressBar IsNot Nothing Then
                oProgressBar.Maximum = intNumeroTabelle
            End If

            strConnectionString = oConnectionString.ToString
            Using cnConnection As New MySqlConnection(strConnectionString)
                cnConnection.Open()
                trTrans = cnConnection.BeginTransaction

                If inConControlloIniziale Then
                    ' **** Controllo iniziale per evitare di effettuare l'operazione sempre ****
                    If TableExists("TasksHours") Then Return True
                End If

                If Not ControlloEsistenzaTabella("Backlogs", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Topics", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Activities", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Sprints", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Projects", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("UserStories", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Meetings", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                If Not ControlloEsistenzaTabella("Tasks", cnConnection, trTrans, oProgressBar, oLabel) Then Return False

                If Not ControlloEsistenzaTabella("TasksHours", cnConnection, trTrans, oProgressBar, oLabel) Then Return False
                ' **** ricorda di impostare il controllo iniziale *****

                trTrans.Commit()
            End Using

            Return True
        Catch ex As Exception
            If trTrans IsNot Nothing Then
                trTrans.Rollback()
            End If
            strError = ex.Message
            Return False
        End Try
    End Function

    Protected Overrides Sub MostraErrore()
        MsgBox("Errore nella creazione delle tabella: " & strNameTable & vbCrLf & strError)
    End Sub

    Protected Overrides Sub DropDB()
        strConnectionString = String.Format("host={0}; username={1}; password={2};", oConnectionString.Host, oConnectionString.UserName, oConnectionString.Password)

        Using cnConnection As New MySqlConnection(strConnectionString)
            cnConnection.Open()
            Dim strSql As String = String.Format("drop schema `{0}`", strNomeDb)
            Using cmCommand As New MySqlCommand(strSql, cnConnection)
                cmCommand.ExecuteNonQuery()
            End Using
        End Using
    End Sub

#Region "Tabelle"

    Protected Overrides Function CreaTabella(ByVal inNameTable As String, ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            strNameTable = inNameTable
            Select Case inNameTable.ToUpper
                Case "Backlogs".ToUpper : Return CreateTable_Backlogs(inCn, inTrans)
                Case "Topics".ToUpper : Return CreateTable_Topics(inCn, inTrans)
                Case "Activities".ToUpper : Return CreateTable_Activities(inCn, inTrans)
                Case "Projects".ToUpper : Return CreateTable_Projects(inCn, inTrans)
                Case "Sprints".ToUpper : Return CreateTable_Sprints(inCn, inTrans)
                Case "UserStories".ToUpper : Return CreateTable_UserStories(inCn, inTrans)
                Case "Meetings".ToUpper : Return CreateTable_Meetings(inCn, inTrans)
                Case "Tasks".ToUpper : Return CreateTable_Tasks(inCn, inTrans)
                Case "TasksHours".ToUpper : Return CreateTable_TasksHours(inCn, inTrans)

                Case "TabOpzioni".ToUpper : Return CreaTabellaTabOpzioni(inCn, inTrans)
                Case Else
                    Return False
            End Select
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreaTabellaTabOpzioni(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Backlogs(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`backlogs` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(45) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) ) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Topics(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`topics` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(45) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) ) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Activities(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`activities` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(45) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) ) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Projects(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`projects` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(10) NULL DEFAULT NULL , " &
                    "  `Description` LONGTEXT NULL DEFAULT NULL , " &
                    "  `StartDate` DATE NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) ) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Sprints(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`sprints` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(10) NULL DEFAULT NULL , " &
                    "  `StartDate` DATE NULL DEFAULT NULL , " &
                    "  `BacklogsID` INT(11) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) , " &
                    "  CONSTRAINT `FK_BacklogsID_Sprints` " &
                    "    FOREIGN KEY (`BacklogsID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`backlogs` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Meetings(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`meetings` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Name` VARCHAR(45) NULL DEFAULT NULL , " &
                    "  `Description` LONGTEXT NULL DEFAULT NULL , " &
                    "  `Date` DATE NULL DEFAULT NULL , " &
                    "  `Time` VARCHAR(5) NULL DEFAULT NULL , " &
                    "  `TopicsID` INT(11) NULL DEFAULT NULL , " &
                    "  `UserStoriesID` INT(11) NULL DEFAULT NULL , " &
                    "  `Minute` LONGTEXT NULL DEFAULT NULL , " &
                    "  `Status` TINYINT(4) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) , " &
                    "  CONSTRAINT `FK_TopicsID_Meetings` " &
                    "    FOREIGN KEY (`TopicsID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`topics` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION, " &
                    "  CONSTRAINT `FK_UserStoriesID_Meetings` " &
                    "    FOREIGN KEY (`UserStoriesID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`userstories` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; " &
                    " " &
                    "CREATE INDEX `IND_UserStoriesID_Meetings` ON `" & strNomeDb & "`.`meetings` (`UserStoriesID` ASC) ; " &
                    " " &
                    "CREATE INDEX `IND_TopicsID_Meetings` ON `" & strNomeDb & "`.`meetings` (`TopicsID` ASC) ; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_UserStories(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`userstories` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Number` VARCHAR(10) NULL DEFAULT NULL , " &
                    "  `Name` VARCHAR(45) NULL DEFAULT NULL , " &
                    "  `Description` VARCHAR(255) NULL DEFAULT NULL , " &
                    "  `Done` VARCHAR(255) NULL DEFAULT NULL , " &
                    "  `Priority` int(11) NULL DEFAULT NULL , " &
                    "  `SprintsID` INT(11) NULL DEFAULT NULL , " &
                    "  `ProjectsID` INT(11) NULL DEFAULT NULL , " &
                    "  `Status` TINYINT(4) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) , " &
                    "  CONSTRAINT `FK_SprintsID_UserStories` " &
                    "    FOREIGN KEY (`SprintsID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`sprints` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION, " &
                    "  CONSTRAINT `FK_ProjectsID_UserStories` " &
                    "    FOREIGN KEY (`ProjectsID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`projects` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; " &
                    " " &
                    "CREATE INDEX `IND_SprintsID_UserStories` ON `" & strNomeDb & "`.`userstories` (`SprintsID` ASC) ; " &
                    " " &
                    "CREATE INDEX `IND_ProjectsID_UserStories` ON `" & strNomeDb & "`.`userstories` (`ProjectsID` ASC) ; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_Tasks(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`tasks` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `Order` int(11) NULL DEFAULT NULL , " &
                    "  `Description` VARCHAR(255) NULL DEFAULT NULL , " &
                    "  `ActivitiesID` INT(11) NULL DEFAULT NULL , " &
                    "  `UserStoriesID` INT(11) NULL DEFAULT NULL , " &
                    "  `ExpectedHours` decimal(10) NULL DEFAULT NULL , " &
                    "  `Status` TINYINT(4) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) , " &
                    "  CONSTRAINT `FK_ActivitiesID_Tasks` " &
                    "    FOREIGN KEY (`ActivitiesID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`Activities` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION, " &
                    "  CONSTRAINT `FK_UserStoriesID_Tasks` " &
                    "    FOREIGN KEY (`UserStoriesID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`userstories` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; " &
                    " " &
                    "CREATE INDEX `IND_UserStoriesID_Tasks` ON `" & strNomeDb & "`.`tasks` (`UserStoriesID` ASC) ; " &
                    " " &
                    "CREATE INDEX `IND_ActivitiesID_Tasks` ON `" & strNomeDb & "`.`tasks` (`ActivitiesID` ASC) ; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

    Private Function CreateTable_TasksHours(ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Dim strSql As String = "" &
                    "CREATE  TABLE IF NOT EXISTS `" & strNomeDb & "`.`taskshours` ( " &
                    "  `ID` int(11) NOT NULL AUTO_INCREMENT , " &
                    "  `TasksID` INT(11) NULL DEFAULT NULL , " &
                    "  `Hours1` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours2` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours3` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours4` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours5` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours6` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours7` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours8` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours9` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours10` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours11` TINYINT(4) NULL DEFAULT NULL , " &
                    "  `Hours12` TINYINT(4) NULL DEFAULT NULL , " &
                    "  PRIMARY KEY (`ID`) , " &
                    "  CONSTRAINT `FK_TasksID_TasksHours` " &
                    "    FOREIGN KEY (`TasksID` ) " &
                    "    REFERENCES `" & strNomeDb & "`.`Tasks` (`ID` ) " &
                    "    ON DELETE NO ACTION " &
                    "    ON UPDATE NO ACTION) " &
                    "ENGINE = InnoDB " &
                    "DEFAULT CHARACTER SET = latin1; " &
                    " " &
                    "CREATE INDEX `IND_TasksID_TasksHours` ON `" & strNomeDb & "`.`taskshours` (`TasksID` ASC) ; "

            Using cmCommand As New MySqlCommand(strSql, inCn)
                cmCommand.Transaction = inTrans
                cmCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

#End Region

#Region "Campi"

    Protected Overloads Function CreaCampo(ByVal inNameTable As String, ByVal inNameCampo As String, ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Select Case inNameTable.ToUpper & inNameCampo.ToUpper
                Case "Compresi".ToUpper & "Nome".ToUpper

            End Select
            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

#End Region

End Class
